from sqlalchemy import create_engine
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import sessionmaker

from sqlalchemy import create_engine, and_, or_
from sqlalchemy.orm import sessionmaker
from sqlalchemy.exc import SQLAlchemyError
from commons.entity.po.travel import Travel
from commons.entity.po.reimbursement import Reimbursement
from commons.entity.po.approval import Approval


from commons.entity.po.approval import Approval
from commons.entity.po.reimbursement import Reimbursement
from commons.entity.po.travel import Travel
from commons.mappers.base_mapper import BaseMapper



# 创建数据库引擎
db_url = "mysql+pymysql://root:123456@localhost/ctdb"
engine = create_engine(db_url, echo=True)

# 创建会话类
Session = sessionmaker(bind=engine)


class TravelMapper(BaseMapper):
    def __init__(self):
        super().__init__(Session)

    # def insert(self, travel_data):
    #     """
    #     插入记录
    #     :param travel_data:
    #     :return: 成功返回 True， 否则 False
    #     """
    #     print(travel_data)
    #     try:
    #         with self.session_factory() as session:
    #             travel = Travel(
    #                 travel_reason=travel_data["travel_reason"] if travel_data["travel_reason"] is not None else None,
    #                 travel_detail=travel_data["travel_detail"] if travel_data["travel_detail"] is not None else None,
    #                 apply_date=travel_data["apply_date"] if travel_data["apply_date"] is not None else None,
    #                 start_date=travel_data["start_date"] if travel_data["start_date"] is not None else None,
    #                 end_time=travel_data["end_time"] if travel_data["end_time"] is not None else None,
    #                 food_cost=travel_data["food_cost"] if travel_data["food_cost"] is not None else None,
    #                 transport_cost=travel_data["transport_cost"] if travel_data["transport_cost"] is not None else None,
    #                 hotel_cost=travel_data["hotel_cost" if travel_data["hotel_cost"] is not None else None],
    #                 cost_detail=travel_data["cost_detail" if travel_data["cost_detail"] is not None else None],
    #                 advance_payment=travel_data["advance_payment"] if travel_data["advance_payment"] is not None else None,
    #                 user_id=travel_data["user_id"] if travel_data["user_id"] is not None else None,
    #             )
    #             print(travel)
    #             session.add(travel)
    #             session.commit()
    #             return True
    #     except SQLAlchemyError as e:
    #         session.rollback()
    #         print(f"Database error during insert operation: {str(e)}")
    #         return False

    def select_by_travel_id(self, travel_id):
        """
        根据 travel_id 从数据库获取 Travel 对象
        :param travel_id: str
        :return: Travel 对象或 None
        """
        try:
            with self.session_factory() as session:
                travel = session.query(Travel).filter_by(travel_id=travel_id).first()
                if travel:
                    return travel
                return None
        except SQLAlchemyError as e:
            print(f"Database error: {str(e)}")
            return None

    def delete_by_travel_id(self, travel_id):
        """
        根据 travel_id 删除出差记录
        :param travel_id: str
        :return: True（删除成功）或 False（失败）
        """
        try:
            with self.session_factory() as session:
                travel = session.query(Travel).filter_by(travel_id=travel_id).first()
                if travel:
                    session.delete(travel)
                    session.commit()  # 提交事务
                    return True
                return False  # 出差记录不存在
        except SQLAlchemyError as e:
            print(f"Database error during delete operation: {str(e)}")
            return False

    def update_by_travel_id(self, travel_update, travel_id):
        """
        根据 travel_id 更新出差记录
        :param travel_id: str
        :param travel_update: Travel 类实例或字典，包含需要更新的出差记录属性
        :return: True（更新成功）或 False（失败）
        """
        try:
            with self.session_factory() as session:
                travel = session.query(Travel).filter_by(travel_id=travel_id).first()
                if travel:
                    # 如果 travel_update 是类实例，转换为字典
                    if travel_update and hasattr(travel_update, 'to_dict'):
                        travel_update = travel_update.to_dict()

                    # 使用新数据更新出差记录属性
                    for key, value in travel_update.items():
                        if value is not None and hasattr(travel, key):
                            setattr(travel, key, value)

                    session.commit()  # 提交事务
                    return True
                return False  # 出差记录不存在
        except SQLAlchemyError as e:
            print(f"Database error during update operation: {str(e)}")
            return False

    def select_final_by_user_id(self, user_id):
        """
        根据 user_id 获取出差信息
        :param user_id: str
        :return: 出差记录列表
        """
        try:
            with self.session_factory() as session:
                query = session.query(
                    Travel.travel_id,
                    Travel.travel_reason,
                    Travel.travel_detail,
                    Travel.apply_date,
                    Travel.start_date,
                    Travel.end_date,
                    Travel.food_cost,
                    Travel.transport_cost,
                    Travel.hotel_cost,
                    Travel.cost_detail,
                    Travel.advance_payment,
                    Travel.user_id
                ).join(
                    Reimbursement, Travel.travel_id == Reimbursement.travel_id, isouter=True
                ).join(
                    Approval, and_(
                        Travel.travel_id == Approval.travel_id,
                        Travel.user_id == Approval.user_id
                    ), isouter=True
                ).filter(
                    or_(
                        and_(
                            Reimbursement.travel_id == None,
                            Approval.travel_approval == 1,
                            Approval.reimbursement_approval_ma == 0
                        ),
                        and_(
                            Reimbursement.travel_id == Travel.travel_id,
                            Approval.reimbursement_approval_ma == 2
                        )
                    ),
                    Travel.user_id == user_id
                ).all()

                return query

        except SQLAlchemyError as e:
            print(f"Database error: {str(e)}")
            return None

    def select_by_user_id(self, user_id):
            """
            根据 user_id 从数据库获取 Travel 对象
            :param user_id: str
            :return: Travel 对象或 None
            """
            try:
                with self.session_factory() as session:
                    travels = session.query(Travel).filter_by(user_id=user_id).all()
                    if travels:
                        print(travels)
                        return travels
                    return None
            except SQLAlchemyError as e:
                print(f"Database error: {str(e)}")
                return None